/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import model.Etudiant;
import model.Note;

/**
 *
 * @author Paul
 */
public class DAONote extends DAO<Note> {

    public void create(Note obj) {
        String query = "";
        changeGeneral(query);
    }

    public void create(Note obj, int idEtudiant, int idExamen) {
        String query = "insert into note (id_examen,id_eleve,note) values (?,?,?)";//preparation de la requete
        /**initialisation des variables **/
        int nb = 0;
        int autoIncKeyFromApi = -1;
        PreparedStatement  ps=null;

       /**lancement de la connexion à la bd et de la requete **/
        try {
            Connection conn = DBConnection.getConnection();//Creation de la connexion
            ps= conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);//preparation de la requete
            
        /** remplacement des points d'interogation dans la query par les variables**/  
            ps.setInt(1,idExamen);
            ps.setInt(2,idEtudiant);
            ps.setFloat(3, obj.getNote());  
            
            
            nb = ps.executeUpdate();//lancement de la requête
            ResultSet rs = ps.getGeneratedKeys();//recupération du résultat
         
            if(rs.next()){//récuperation de l'id 
                autoIncKeyFromApi = rs.getInt(1);
                System.out.println(autoIncKeyFromApi);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public void update(Note obj) {
        String query = "";
        changeGeneral(query);
    }

    public void delete(Note obj) {
        String query = "";
        changeGeneral(query);
    }

    public ArrayList<Note> getAll() {
        String query = "";
        return findGeneral(query);
    }

    public Note find(int id) {
        String query = "";
        Note note = null;
        note = findGeneral(query).get(0);
        return note;
    }

    /**
     * retourne la note d'un exam d'un étudiant
    **/
    public Note findNoteEtudiantExam(int id_eleve,int id_exam) {
        String query = "select * from note where id_eleve="+ id_eleve +" and id_examen='"+id_exam+"';";
        Note note = null;
        note = findGeneral(query).get(0);
        return note;
    }
    
        /**
     * retourne les notes d'un étudiant pour une matière. ROmain : cette methode n'est pas implémenté caril faut des boucles, il faut récuperer tous les exam d'un cour à partir de ca il faut récuperer chaque note pour ces exam via la fonction findNoteEtudiantExam

    public ArrayList<Note> findNoteEtudiantMatiere(int id_eleve,int id_matiere) {
String query="select n.id_note, n.id_eleve, n.id_examen, n.note from cour_exam_asso cea, examen exam, note n where  cea.id_examen=exam.id_examen and exam.id_examen=n.id_examen and n.id_eleve="+id_eleve+" and cea.id_cour="+id_matiere+";";
        ArrayList<Note> notes = new ArrayList<Note>();
        Note note = null;
        Statement  ps=null;
        try {
            Connection conn = DBConnection.getConnection();
            ps= conn.createStatement();
            ResultSet res = ps.executeQuery(query);
            while (res.next()) {
            int idNnote = res.getInt("id_note");
             float note_e = res.getFloat("note");
            
                note = new Note(id_note,note_e);
                notes.add(note);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return notes;
    }
    *     **/
//---------------------------------------------------------private method--------------------------------------------------------------------//
    private int changeGeneral(String query) {
        Statement st = null;
        int nb = 0;
        try {
            Connection conn = DBConnection.getConnection();
            st = conn.createStatement();
            nb = st.executeUpdate(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return nb;
    }

    private ArrayList<Note> findGeneral(String requete) {
        ArrayList<Note> notes = new ArrayList<Note>();
        Statement st = null;
        Note note = null;

        try {
            Connection conn = DBConnection.getConnection();
            st = conn.createStatement();
            ResultSet res = st.executeQuery(requete);
            while (res.next()) {
                note = createObject(res);
                notes.add(note);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return notes;
    }

    private Note createObject(ResultSet res) {
        Note note = null;
        try {
            int id = res.getInt("id_note");
            Float notec = res.getFloat("note");
            note = new Note(id, notec);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return note;
    }
}
